from typing import Any, Dict, Optional, Union, List

from sqlalchemy import select
from sqlalchemy.ext.asyncio import AsyncSession

from app.models.report import Report, ReportInstance
from app.schemas.report import ReportCreate, ReportUpdate, ReportInstanceCreate


async def get_report_by_id(db: AsyncSession, *, id: int) -> Optional[Report]:
    return await db.get(Report, id)


async def get_reports(
    db: AsyncSession, *, skip: int = 0, limit: int = 100,
    report_type: Optional[str] = None
) -> List[Report]:
    query = select(Report)
    if report_type:
        query = query.where(Report.report_type == report_type)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_report(db: AsyncSession, *, obj_in: ReportCreate, creator_id: int) -> Report:
    db_obj = Report(
        name=obj_in.name,
        description=obj_in.description,
        report_type=obj_in.report_type,
        template=obj_in.template,
        parameters=obj_in.parameters,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def update_report(
    db: AsyncSession, *, db_obj: Report, obj_in: Union[ReportUpdate, Dict[str, Any]]
) -> Report:
    if isinstance(obj_in, dict):
        update_data = obj_in
    else:
        update_data = obj_in.dict(exclude_unset=True)
    for field in update_data:
        setattr(db_obj, field, update_data[field])
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_report(db: AsyncSession, *, id: int) -> Report:
    report = await db.get(Report, id)
    await db.delete(report)
    await db.commit()
    return report


async def get_report_instance_by_id(db: AsyncSession, *, id: int) -> Optional[ReportInstance]:
    return await db.get(ReportInstance, id)


async def get_report_instances(
    db: AsyncSession, *, report_id: int, skip: int = 0, limit: int = 100,
    project_id: Optional[int] = None
) -> List[ReportInstance]:
    query = select(ReportInstance).where(ReportInstance.report_id == report_id)
    if project_id:
        query = query.where(ReportInstance.project_id == project_id)
    query = query.offset(skip).limit(limit)
    result = await db.execute(query)
    return result.scalars().all()


async def create_report_instance(
    db: AsyncSession, *, obj_in: ReportInstanceCreate, creator_id: int
) -> ReportInstance:
    db_obj = ReportInstance(
        report_id=obj_in.report_id,
        project_id=obj_in.project_id,
        parameters=obj_in.parameters,
        data=obj_in.data,
        creator_id=creator_id,
    )
    db.add(db_obj)
    await db.commit()
    await db.refresh(db_obj)
    return db_obj


async def delete_report_instance(db: AsyncSession, *, id: int) -> ReportInstance:
    report_instance = await db.get(ReportInstance, id)
    await db.delete(report_instance)
    await db.commit()
    return report_instance